"""
数据库管理模块
"""

import pymysql
from typing import List, Optional, Dict, Any
from contextlib import contextmanager
from ..config import ConfigManager


class DatabaseManager:
    """数据库管理器"""
    
    def __init__(self, config_manager: ConfigManager = None):
        """
        初始化数据库管理器
        
        Args:
            config_manager: 配置管理器
        """
        self.config_manager = config_manager or ConfigManager()
        self.db_config = self.config_manager.get_database_config()
        self.greeting_config = self.config_manager.get_greeting_config()
    
    @contextmanager
    def get_connection(self):
        """
        获取数据库连接（上下文管理器）
        
        Yields:
            数据库连接
        """
        connection = None
        try:
            connection = pymysql.connect(
                host=self.db_config['host'],
                port=self.db_config['port'],
                user=self.db_config['user'],
                password=self.db_config['pass'],
                database=self.db_config['dbName'],
                charset='utf8mb4',
                autocommit=False
            )
            yield connection
        except Exception as e:
            if connection:
                connection.rollback()
            raise e
        finally:
            if connection:
                connection.close()
    
    def get_message(self) -> str:
        """
        获取随机消息
        
        Returns:
            消息内容
        """
        try:
            with self.get_connection() as conn:
                cursor = conn.cursor()
                cursor.execute(
                    "SELECT message FROM wt_message ORDER BY RAND() LIMIT 1"
                )
                row = cursor.fetchone()
                
                if row:
                    return row[0]
                else:
                    return self.greeting_config.get('message', '你好，可以加个好友吗，我们进一步聊聊?')
        except Exception as e:
            print(f"获取消息失败: {e}")
            return self.greeting_config.get('message', '你好，可以加个好友吗，我们进一步聊聊?')
    
    def get_mobiles(self, start: int = 0, limit: int = 10) -> List[str]:
        """
        获取手机号列表
        
        Args:
            start: 起始位置
            limit: 限制数量
            
        Returns:
            手机号列表
        """
        try:
            with self.get_connection() as conn:
                cursor = conn.cursor()
                cursor.execute(
                    "SELECT DISTINCT mobile FROM wt_customer "
                    "LIMIT %s, %s",
                    (start, limit)
                )
                rows = cursor.fetchall()
                
                mobiles = [row[0] for row in rows if row[0]]
                return mobiles
        except Exception as e:
            print(f"获取手机号失败: {e}")
            return []
    
    def get_mobile_count(self) -> int:
        """
        获取手机号总数
        
        Returns:
            手机号总数
        """
        try:
            with self.get_connection() as conn:
                cursor = conn.cursor()
                cursor.execute(
                    "SELECT COUNT(DISTINCT mobile) FROM wt_customer"
                )
                row = cursor.fetchone()
                return row[0] if row else 0
        except Exception as e:
            print(f"获取手机号总数失败: {e}")
            return 0
    
    def add_mobile(self, mobile: str) -> bool:
        """
        添加手机号
        
        Args:
            mobile: 手机号
            
        Returns:
            是否成功
        """
        try:
            with self.get_connection() as conn:
                cursor = conn.cursor()
                cursor.execute(
                    "INSERT INTO wt_customer (mobile) VALUES (%s)",
                    (mobile,)
                )
                conn.commit()
                return True
        except Exception as e:
            print(f"添加手机号失败: {e}")
            return False
    
    def remove_mobile(self, mobile: str) -> bool:
        """
        删除手机号
        
        Args:
            mobile: 手机号
            
        Returns:
            是否成功
        """
        try:
            with self.get_connection() as conn:
                cursor = conn.cursor()
                cursor.execute(
                    "DELETE FROM wt_customer WHERE mobile = %s",
                    (mobile,)
                )
                conn.commit()
                return True
        except Exception as e:
            print(f"删除手机号失败: {e}")
            return False
    
    def add_message(self, message: str) -> bool:
        """
        添加消息模板
        
        Args:
            message: 消息内容
            
        Returns:
            是否成功
        """
        try:
            with self.get_connection() as conn:
                cursor = conn.cursor()
                cursor.execute(
                    "INSERT INTO wt_message (message) VALUES (%s)",
                    (message,)
                )
                conn.commit()
                return True
        except Exception as e:
            print(f"添加消息失败: {e}")
            return False
    
    def get_all_messages(self) -> List[str]:
        """
        获取所有消息模板
        
        Returns:
            消息列表
        """
        try:
            with self.get_connection() as conn:
                cursor = conn.cursor()
                cursor.execute(
                    "SELECT message FROM wt_message ORDER BY id"
                )
                rows = cursor.fetchall()
                return [row[0] for row in rows]
        except Exception as e:
            print(f"获取消息列表失败: {e}")
            return []
    
    def test_connection(self) -> bool:
        """
        测试数据库连接
        
        Returns:
            连接是否成功
        """
        try:
            with self.get_connection() as conn:
                cursor = conn.cursor()
                cursor.execute("SELECT 1")
                return True
        except Exception as e:
            print(f"数据库连接测试失败: {e}")
            return False
